package kevin.framework.basic.common.xls;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import kevin.framework.basic.exception.FrameException;

/**
 * @ClassName: XlsOperator
 * @Description: xls导出导入通用操作类
 * @author kevin.huang
 * @date 2015-1-21 上午10:54:08
 * @param <T>
 */
@SuppressWarnings("deprecation")
public class XlsOperator {

	XlsCellData[] preRowData = null;// 前一行数据，用于数据行合并
	HSSFRow preXlsRow = null;// xls的前一行，用于数据行合并

	HSSFWorkbook workbook;

	CellStyleCache styleCache;

	public XlsOperator(CellStyleCache styleCache) {
		this.styleCache = styleCache;
	}

	static final double sizelimit = 60000;

	public HSSFCellStyle getCellStyle(CustomCellStyle styleDefined) {
		String cacheKey = styleDefined.toString();
		HSSFCellStyle style = styleCache.getStyle(cacheKey);
		if (style != null) {
			return style;
		}
		style = workbook.createCellStyle();
		style.setFillForegroundColor(styleDefined.cellBackground);

		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// 设置底边框;
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);

		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		style.setRightBorderColor(HSSFColor.BLACK.index);
		style.setTopBorderColor(HSSFColor.BLACK.index);

		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		if (!"".equals(styleDefined.dataFormat)) {
			HSSFDataFormat format = workbook.createDataFormat();
			style.setDataFormat(format.getFormat(styleDefined.dataFormat));
		}
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints(styleDefined.fontHeightInPoints);
		font.setFontName(styleDefined.fontName);
		font.setColor(styleDefined.fontColor);
		font.setBoldweight(styleDefined.fontWeight);
		style.setFont(font);
		styleCache.setStyle(cacheKey, style);
		return style;
	}

	/**
	 * @Title: outXls 输出数据到xls模板文件 按模板导出不支持生成多个sheet
	 * @param FileInputStream xls模板文件的输入流
	 * @param sheetName       sheet名称
	 * @param startRow        开始填充的行号
	 * @param dataList        数据列表
	 * @param opertor         实现了xls操作接口的对象
	 * @return 无返回
	 * @throws Throwable
	 */
	public <T> void outXlsByModel(OutputStream outStream, FileInputStream modelFileStream, String sheetName,
			int startRow, List<T> dataList, IXlsOperator<T> opertor) throws Throwable {
		workbook = new HSSFWorkbook(modelFileStream);
		HSSFSheet sheet = workbook.getSheet(sheetName); // 读取工作簿
		HSSFRow rowTitle = sheet.getRow(startRow - 1);
		Short cellNum = rowTitle.getLastCellNum();
		Object[] dataSumArr = new Object[cellNum];
		CustomCell[] titleArr = new CustomCell[cellNum];
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		int ss = startRow - 1;
		int j = 0;
		while (j < cellNum) {
			HSSFCell cell = rowTitle.getCell(j);
			String value = getCellFormatValue(cell, evaluator);
			titleArr[j] = new CustomCell(value);
			j++;
		}
		HSSFCellStyle style = getCellStyle(new CustomCellStyle());
		for (T bean : dataList) {
			HSSFRow row = sheet.createRow(startRow);
			XlsCellData[] cellData = opertor.xlsExport(bean, startRow);
			createRow(sheet, startRow, dataSumArr, row, cellData, opertor, bean, titleArr, style);
			startRow++;
		}
		if (dataList.size() > 0) {
			XlsCellData[] sumArr = opertor.onSheetCreateCompleted(dataSumArr, startRow - ss, startRow);
			if (sumArr != null) { // 创建最后一行总结行
				HSSFRow row11 = sheet.createRow(startRow);
				createRow(sheet, startRow, dataSumArr, row11, sumArr, opertor, null, titleArr, style);
			}
		}
		workbook.write(outStream);
		styleCache.clear();
		workbook.close();
		preRowData = null;
		preXlsRow = null;
	}

	/**
	 * @Title: outNewComplexXls 复杂表头类型导出
	 * @param newXlsFile 新的xls临时文件
	 * @param headList   List<CustomCell[]> 自定义可跨行跨列的多表头
	 * @param sheetName  sheetName
	 * @param dataList   数据列表
	 * @param opertor    实现了xls操作接口的对象
	 * @return void
	 * @throws Throwable
	 */
	public <T> File outNewComplexXls(String newXlsFile, List<CustomCell[]> headList, String sheetName, List<T> dataList,
			IXlsOperator<T> opertor) throws Throwable {
		workbook = new HSSFWorkbook();
		;
		double c = (double) dataList.size() / sizelimit;
		double sheetCount = Math.ceil(c); // 当超过60000行时候 产生多个sheet
		if (sheetCount == 0) {
			sheetCount = 1;
		}
		int listStart = 0;
		for (int i = 0; i < sheetCount; i++) {
			preRowData = null;
			preXlsRow = null;
			if (i > 0) {
				sheetName = sheetName + i;
			}
			HSSFSheet sheet = workbook.createSheet(sheetName);
			listStart = createSheet(sheet, headList, 0, dataList, opertor, listStart);
		}
		File file = new File(newXlsFile);
		file.createNewFile();
		OutputStream outPutStream = new FileOutputStream(file);
		workbook.write(outPutStream);
		outPutStream.flush();
		outPutStream.close();
		styleCache.clear();
		workbook.close();
		preRowData = null;
		preXlsRow = null;
		return file;
	}

	/**
	 * @Title: outNewComplexXls 复杂表头类型导出
	 * @param outStream ServletOutStream
	 * @param headList  List<CustomCell[]> 自定义表头
	 * @param sheetName sheetName
	 * @param dataList  数据列表
	 * @param opertor   实现了xls操作接口的对象
	 * @return void
	 * @throws Throwable
	 */
	public <T> void outNewComplexXls(OutputStream outStream, List<CustomCell[]> headList, String sheetName,
			List<T> dataList, IXlsOperator<T> opertor) throws Throwable {
		workbook = new HSSFWorkbook();
		double c = (double) dataList.size() / sizelimit;
		double sheetCount = Math.ceil(c); // 当超过60000行时候 产生多个sheet
		if (sheetCount == 0) {
			sheetCount = 1;
		}
		int listStart = 0;
		for (int i = 0; i < sheetCount; i++) {
			preRowData = null;
			preXlsRow = null;
			if (i > 0) {
				sheetName = sheetName + i;
			}
			HSSFSheet sheet = workbook.createSheet(sheetName);
			listStart = createSheet(sheet, headList, 0, dataList, opertor, listStart);
		}
		workbook.write(outStream);
		styleCache.clear();
		workbook.close();
		preRowData = null;
		preXlsRow = null;
	}

	private <T> int createSheet(HSSFSheet sheet, List<CustomCell[]> headList, int startRow, List<T> dataList,
			IXlsOperator<T> opertor, int listStart) throws Throwable {

		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		CustomCellStyle headerStyle = new CustomCellStyle();
		headerStyle.fontWeight = HSSFFont.BOLDWEIGHT_BOLD;
		headerStyle.fontColor = HSSFColor.WHITE.index;
		headerStyle.cellBackground = HSSFColor.DARK_BLUE.index;
		HSSFCellStyle style = this.getCellStyle(headerStyle);
		// 表格标题行
		for (CustomCell[] cellArr : headList) {
			HSSFRow row = sheet.createRow(startRow);
			int colIndex = 0;
			for (CustomCell c : cellArr) {
				if (c == null) {
					throw new FrameException("the " + startRow + " row's CustomCell Array Index is Error!");
				}
				String txt = c.getText();
				HSSFCell cell;
				HSSFCellStyle aplayStyle = style;
				CustomCellStyle style1 = opertor.createHeaderCellStyle(txt, startRow);
				if (style1 != null) {
					aplayStyle = this.getCellStyle(style1);
				}
				if (c.getColspen() > 0 && c.getRowspen() > 0) {// 跨行跨列
					int endRow = startRow + c.getRowspen() - 1;
					int endCol = colIndex + c.getColspen() - 1;
					sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, colIndex, endCol));
					cell = row.createCell(colIndex);
					int help = colIndex;
					while (help < colIndex) {
						row.createCell(help).setCellStyle(aplayStyle);
						help++;
					}
					colIndex = endCol + 1;
				} else if (c.getRowspen() > 0 && c.getColspen() == 0) {// 跨行
					int endRow = startRow + c.getRowspen() - 1;
					sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, colIndex, colIndex));
					cell = row.createCell(colIndex);
					colIndex++;
				} else if (c.getColspen() > 0 && c.getRowspen() == 0) {// 跨列
					int endIdx = colIndex + c.getColspen() - 1;
					sheet.addMergedRegion(new CellRangeAddress(startRow, startRow, colIndex, endIdx));
					cell = row.createCell(colIndex);
					int help = colIndex;
					colIndex = endIdx + 1;
					while (help < colIndex) {
						row.createCell(help).setCellStyle(aplayStyle);
						help++;
					}
				} else {// 普通单元格
					cell = row.createCell(colIndex);
					colIndex++;
				}
				cell.setCellStyle(aplayStyle);
				cell.setCellValue(txt);
			}
			startRow++;
		}
		HSSFCellStyle style2 = getCellStyle(new CustomCellStyle());
		int size = dataList.size();
		CustomCell[] titleArr = headList.get(headList.size() - 1);
		Object[] dataSumArr = new Object[titleArr.length];
		int rowhelp = 0;
		/// 产生数据行
		for (; listStart < size; listStart++) {
			if (rowhelp > sizelimit) {
				break;
			}
			T bean = dataList.get(listStart);
			XlsCellData[] dataArr = opertor.xlsExport(bean, startRow);
			HSSFRow row1 = sheet.createRow(startRow);
			createRow(sheet, startRow, dataSumArr, row1, dataArr, opertor, bean, titleArr, style2);
			startRow++;
			rowhelp++;
		}
		if (dataList.size() > 0) {
			XlsCellData[] sumArr = opertor.onSheetCreateCompleted(dataSumArr, startRow - headList.size(), startRow);
			if (sumArr != null) { // 创建最后一行总结行
				HSSFRow row11 = sheet.createRow(startRow);
				createRow(sheet, startRow, dataSumArr, row11, sumArr, opertor, null, titleArr, style2);
			}
		}
		return listStart;
	}

	private <T> void createRow(HSSFSheet sheet, int startRow, Object[] dataSumArr, HSSFRow row1, XlsCellData[] dataArr,
			IXlsOperator<T> opertor, T bean, CustomCell[] titleArr, HSSFCellStyle style) throws Exception {
		for (int i = 0, len = dataArr.length; i < len; i++) {
			XlsCellData cdata = dataArr[i];
			if (cdata == null) {
				continue;
			}
			CustomCell ccell = titleArr[i];
			HSSFCell cell = row1.createCell(i);
			CustomCellStyle styleTmp = opertor.createBodyCellStyle(bean, cdata, ccell.getText());
			if (styleTmp == null) {
				cell.setCellStyle(style);
			} else {
				cell.setCellStyle(this.getCellStyle(styleTmp));
			}

			// 行合并实现
			if (ccell.isRowGroup() && preRowData != null) { // 该列为分组合并
				if ((preRowData[i].data == null && cdata.data == null) || preRowData[i].data.equals(cdata.data)) { // 前一行和当前行数据是一样的则合并行
					sheet.addMergedRegion(new CellRangeAddress(startRow - 1, startRow, i, i));
				}
			}
			switch (cdata.type) {
			case cFloat:
				double v = Double.valueOf(String.valueOf(cdata.data));
				Object obj = dataSumArr[i];
				dataSumArr[i] = (obj == null ? 0 : Double.valueOf(String.valueOf(obj))) + v;
				cell.setCellValue(v);
				break;
			case cInteger:
				Integer v1 = Integer.valueOf(String.valueOf(cdata.data));
				Object obj1 = dataSumArr[i];
				dataSumArr[i] = (obj1 == null ? 0 : Integer.valueOf(String.valueOf(obj1))) + v1;
				cell.setCellValue(v1);
				break;
			case cString:
				cell.setCellValue(String.valueOf(cdata.data));
				break;
			case cDate:
				cell.setCellValue((Date) cdata.data);
				break;
			case cFormula:
				cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
				cell.setCellFormula(String.valueOf(cdata.data));
				break;
			default:
				cell.setCellValue(new HSSFRichTextString(String.valueOf(cdata.data)));
				break;
			}

		}
		preRowData = dataArr;
		preXlsRow = row1;
	}

	/*************** 以下为xls导入 静态方法 *************************/
	/**
	 * 文件方式导入xls
	 * 
	 * @param filePath  待导入的xls文件全路径
	 * @param sheetName 待导入的sheet
	 * @param startRow  从第几行开始是数据行 （从0开始计算）
	 * @param opertor   xls操作 接口
	 * @param errorData 转化为实体时，发生错误数据
	 * @param extParams request中的参数
	 * @return List<T> 实体列表
	 * @throws Throwable
	 */
	public static <T> List<T> inputXls(String filePath, String sheetName, int startRow, IXlsOperator<T> opertor,
			Map<Integer, String[]> errorData, Map<String, String[]> extParams, Map<Object, Object> validDataMap)
			throws Throwable {
		InputStream ins = new FileInputStream(filePath);
		return inputXls(ins, sheetName, startRow, opertor, errorData, extParams, validDataMap);
	}

	/**
	 * 流方式导入xls
	 * 
	 * @param is        xls输入流
	 * @param sheetName 待导入的sheet
	 * @param startRow  从第几行开始是数据行 （从0开始计算）
	 * @param opertor   xls操作 接口
	 * @param errorData 转化为实体时，发生错误数据
	 * @param extParams request中的参数
	 * @return List<T> 实体列表
	 * @throws Throwable
	 */
	public static <T> List<T> inputXls(InputStream is, String sheetNames, int _startRow, IXlsOperator<T> opertor,
			Map<Integer, String[]> errorData, Map<String, String[]> extParams, Map<Object, Object> validDataMap)
			throws Throwable {
		if (opertor == null) {
			return null;
		}
		List<T> resList = new ArrayList<T>();
		Workbook wb = WorkbookFactory.create(is);
		List<Sheet> sheets = new ArrayList<Sheet>();

		if (StringUtils.isNotEmpty(sheetNames)) {
			String[] sheetNameArr = sheetNames.split(";");
			for (String sh : sheetNameArr) {
				sheets.add(wb.getSheet(sh));
			}
		} else {
			Iterator<Sheet> its = wb.sheetIterator();
			while (its.hasNext()) {
				sheets.add(its.next());
			}
		}
		for (Sheet sheet : sheets) {
			boolean isMerged = hasMerged(sheet);
			int startRow = _startRow;
			FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
			int rowNum = sheet.getLastRowNum();
			int colNum = 0;
			while (startRow <= rowNum) {
				Row row = sheet.getRow(startRow);
				if (colNum == 0) {
					colNum = row.getPhysicalNumberOfCells();
				}
				int j = 0;
				String[] dataArray = new String[colNum + 1];
				while (j < colNum) {
					Cell tmpCell = row.getCell(j);
					if (tmpCell != null) {
						if (isMerged && isMergedRegion(sheet, startRow, tmpCell.getColumnIndex())) {
							dataArray[j] = getMergedRegionValue(sheet, startRow, j, evaluator);
						} else {
							dataArray[j] = getCellFormatValue(tmpCell, evaluator).trim();
						}
					}
					j++;
				}
				try {
					XlsConvertResult<T> res = opertor.xlsImport(dataArray, extParams, validDataMap);
					if (res == null) {
						continue;
					}
					if (StringUtils.isEmpty(res.error)) {
						resList.add(res.bean);
					} else {
						dataArray[j] = res.error;
						errorData.put(startRow, dataArray);
					}
				} catch (Exception ex) {
					ex.printStackTrace();
					dataArray[j] = ex.getMessage();
					errorData.put(startRow, dataArray);
				}
				startRow++;
			}
		}
		try {
			is.close();
			wb.close();
		} catch (Exception ex) {
		}
		return resList;
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public static String getMergedRegionValue(Sheet sheet, int row, int column, FormulaEvaluator evaluator) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellFormatValue(fCell, evaluator);
				}
			}
		}
		return null;
	}

	/**
	 * 根据Cell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	private static String getCellFormatValue(Cell cell, FormulaEvaluator evaluator) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellvalue = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				SimpleDateFormat sdf = null;
				double value = cell.getNumericCellValue();
				short f = cell.getCellStyle().getDataFormat();
				if (f == 0) {
					cellvalue = String.valueOf(value);
				} else {
					switch (f) {
					case 14:
					case 31:
					case 191:
						sdf = new SimpleDateFormat("yyyy-MM-dd");
						break;
					case 57:
					case 187:
						sdf = new SimpleDateFormat("yyyy-MM");
						break;
					case 58:
					case 189:
						sdf = new SimpleDateFormat("MM-dd");
						break;
					case 20:
					case 32:
						sdf = new SimpleDateFormat("HH:mm");
						break;
					case 184:
					case 193:
					case 197:
						sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						break;
					case 190:
					case 198:
						sdf = new SimpleDateFormat("hh:mm:ss");
						break;
					case 188:
					case 192:
					case 194:
						sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm");
						break;
					default:
						sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
						break;
					}
					cellvalue = sdf.format(DateUtil.getJavaDate(value));
				}
				break;
			case HSSFCell.CELL_TYPE_FORMULA:
				evaluator.evaluateFormulaCell(cell);
				cellvalue = String.valueOf(cell.getNumericCellValue());
				break;
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
				break;
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 判断合并了行
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	private static boolean isMergedRow(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row == firstRow && row == lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断sheet页中是否含有合并单元格
	 * 
	 * @param sheet
	 * @return
	 */
	private static boolean hasMerged(Sheet sheet) {
		return sheet.getNumMergedRegions() > 0 ? true : false;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row    行下标
	 * @param column 列下标
	 * @return
	 */
	private static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}
}